import pymysql
import datetime

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
list = str(yesterday).split('-')
username = "spider" + "_" + list[0] + "_" + list[1] + "month"


def select_mysql_spider_amount_share(tt):
    conn = pymysql.Connect(
        host='120.79.78.223',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='nx123',  # 密码
        db='spider_all_sum',  # 数据库名
        charset='utf8'  # 连接编码
    )
    cursor = conn.cursor()
    sql_select = "SELECT * FROM {1} WHERE 时间='{0}'".format(tt, username)
    effect_row = cursor.execute(sql_select)
    conn.commit()
    cursor.close()
    conn.close()
    return effect_row


def insert_spider_amount_share(tt, baozou1, baozou2, baozou3, daishu1, daishu2, shenshu, zhangzhongyun1,
                               zhangzhongyun2, tiantianyun1, tiantianyun2, yuewei, weiyueyun, qire, weixiaobao,
                               daishu3, daishu4, zhuishuyun, youshuge, xigua1, xigua2, wangpai, zhangdu1,
                               zhangdu2, baozou4, haoshuyun, seshi, niuniu1, niuniu2, niuniu3, daishu5, incomes):
    conn = pymysql.Connect(
        host='120.79.78.223',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='nx123',  # 密码
        db='spider_all_sum',  # 数据库名
        charset='utf8'  # 连接编码
    )
    cursor = conn.cursor()
    sql_insert = "INSERT INTO {32}(时间,暴走1,暴走2,暴走3,袋鼠1,袋鼠2,神书,掌中云1,掌中云2,天天云1,天天云2,阅微,微阅云,奇热,微小宝," \
                 "袋鼠3,袋鼠4,追书云,有书阁,西瓜1,西瓜2,王牌小说,掌都1,掌都2,暴走情眯,好书云,涩世小说,牛牛推1," \
                 "牛牛推2,牛牛推3,袋鼠5,收入) values('{0}','{1}','{2}'," \
                 "'{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}'," \
                 "'{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}')".format(
        tt, baozou1, baozou2, baozou3, daishu1, daishu2, shenshu, zhangzhongyun1, zhangzhongyun2, tiantianyun1,
        tiantianyun2, yuewei, weiyueyun, qire, weixiaobao, daishu3, daishu4, zhuishuyun, youshuge, xigua1, xigua2,
        wangpai, zhangdu1, zhangdu2, baozou4, haoshuyun, seshi, niuniu1, niuniu2, niuniu3, daishu5, incomes, username)
    # 执行语句
    cursor.execute(sql_insert)
    # 事务提交，否则数据库得不到更新
    conn.commit()
    cursor.close()
    conn.close()


def Update_mysql_spider_amount_share(date_time, baozou1, baozou2, baozou3, daishu1, daishu2, shenshu, zhangzhongyun1,
                                     zhangzhongyun2, tiantianyun1, tiantianyun2, yuewei, weiyueyun, qire, weixiaobao,
                                     daishu3, daishu4, zhuishuyun, youshuge, xigua1, xigua2, wangpai, zhangdu1,
                                     zhangdu2, baozou4, haoshuyun, seshi, niuniu1, niuniu2, niuniu3, daishu5, incomes):
    conn = pymysql.Connect(
        host='120.79.78.223',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='root',  # 用户名
        password='nx123',  # 密码
        db='spider_all_sum',  # 数据库名
        charset='utf8'  # 连接编码
    )
    cursor = conn.cursor()
    sql_update = "UPDATE {32} SET 时间='{0}',暴走1='{1}',暴走2='{2}',暴走3='{3}',袋鼠1='{4}',袋鼠2='{5}',神书='{6}'," \
                 "掌中云1='{7}',掌中云2='{8}',天天云1='{9}',天天云2='{10}',阅微 ='{11}',微阅云='{12}',奇热='{13}',微小宝='{14}'," \
                 "袋鼠3='{15}',袋鼠4='{16}',追书云='{17}',有书阁='{18}',西瓜1='{19}',西瓜2='{20}',王牌小说='{21}',掌都1='{22}'," \
                 "掌都2='{23}',暴走情眯='{24}',好书云='{25}',涩世小说='{26}',牛牛推1='{27}',牛牛推2='{28}'," \
                 "牛牛推3='{29}',袋鼠5='{30}',收入='{31}' WHERE 时间 = '{0}'".format(
        date_time, baozou1, baozou2, baozou3, daishu1, daishu2, shenshu, zhangzhongyun1, zhangzhongyun2, tiantianyun1,
        tiantianyun2, yuewei, weiyueyun, qire, weixiaobao, daishu3, daishu4, zhuishuyun, youshuge, xigua1, xigua2,
        wangpai, zhangdu1, zhangdu2, baozou4, haoshuyun, seshi, niuniu1, niuniu2, niuniu3, daishu5, incomes, username)
    cursor.execute(sql_update)
    conn.commit()
    cursor.close()
    conn.close()